﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ContactsDBConnection$" formatter="bizobjlist of Contact">
	<Sql>
	    SELECT c.id 
		<code><![CDATA[
			DetailLevel details = (DetailLevel)info.get("listDetailLevel");
			if(details > DetailLevel.idOnly)
				sql.Append("dateUpdated, dateCreated ,lastName,firstName	,middleName,phone,mobile,email,isUser,typeId AS type_id,ct.name AS type_name, companyId ");
      if(details == DetailLevel.fullDetails)
        sql.Append(",avitarImage, customTitle, dateOfBirth, gender, location ");
		  ]]></code>
		FROM Contacts c
		LEFT OUTER JOIN ContactType ct on ct.id = c.typeId
		WHERE companyId=@companyId
	<code><![CDATA[
		bool hasCriteria = false;
		string firstPerc = "", secondPerc = "", cx="x";
		//-------- Fitering ---------------------------------------------------		
		if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
		{
			sql.Append(" AND (");
			foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
			{
				cx = filOp.get("criteria") as string;
				if (string.Compare(cx, "s", true) == 0)
					firstPerc = "%";
				else if (string.Compare(cx, "e", true) == 0)
					secondPerc = "%";
				else if (string.Compare(cx, "c", true) == 0)
					firstPerc = secondPerc = "%";
				if (firstPerc.Length > 0 || secondPerc.Length > 0)
					cx = "like";
				if(filOp.get("field") != null && (string)filOp.get("field") != "")
				{
					sql.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
					sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
				}
			}
			sql.Append(") ");
		}
		//-------- Ordering ---------------------------------------------------		
		if(criteria != null && ((IBizObjList)criteria.get("sortOptions")).numElements != 0)
		{
			StringBuilder sortInfo = new StringBuilder(512);
			foreach(IBizObj sortOp in criteria.get("sortOptions") as IBizObjList)
			{
				if(sortInfo.Length > 0)
					sortInfo.Append(",");
				sortInfo.Append(sortOp.get("field"));
				if(Cmn.IsFalse(sortOp.get("isAsc")))
					sortInfo.Append(" DESC");
			}
			sql.Append("order by ").Append(sortInfo);
		}
	]]></code>
	</Sql>
	<Param name="companyId" type="UniqueIdentifier" />
</AdoNetDataAccess>